package com.kgc.javaweb.dao.impl;


import cn.hutool.crypto.SecureUtil;
import cn.hutool.json.JSONUtil;
import com.mysql.jdbc.StringUtils;
import com.kgc.javaweb.dao.UserDao;
import com.kgc.javaweb.dto.UserPageDTO;
import com.kgc.javaweb.entity.*;
import com.kgc.javaweb.enums.CodeMsg;
import com.kgc.javaweb.util.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {


//========================================================================
    //登陆注册--李赛赛
//========================================================================

    @Override
    public UserInfo findByUserNameAndPassword(String username, String password) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select *  from userinfo where username = ?  and password = ?";
        try {
            UserInfo user = queryRunner.query(sql, new BeanHandler<UserInfo>(UserInfo.class), username, password);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("查询失败");
        }
    }

    @Override
    public UserInfo selectByName(String username) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select * from userinfo where username = ?";
        try {
            UserInfo userInfo = queryRunner.query(sql, new BeanHandler<UserInfo>(UserInfo.class), username);
            return userInfo;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void register(String username, String password) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "insert into userinfo (username,password)values(?,?)";
        try {
            int update = queryRunner.update(sql, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public UserInfo selectById(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select *  from userinfo where id = ?";
        try {
            UserInfo user = queryRunner.query(sql, new BeanHandler<UserInfo>(UserInfo.class), id);
            System.out.println("userinfo000 = " + user);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("查询数据失败！");
        }
    }

    @Override
    public void update(UserInfo userInfo) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update userinfo set address = ? ,tel = ? ,birth = ? where username = ?";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String birthday = simpleDateFormat.format(userInfo.getBirth());

        try {
            queryRunner.update(sql, userInfo.getAddress(), userInfo.getTel(), birthday, userInfo.getUsername());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(CodeMsg.UPDATE_FAIL.getMsg());
        }
    }

    @Override
    public void updateUserInfoPwdById(String newPassword, Integer id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update userinfo set password = ?  where id = ?";
        try {
            queryRunner.update(sql, newPassword, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(CodeMsg.UPDATE_FAIL.getMsg());
        }
    }


//========================================================================
    //用户管理--姜智博
//========================================================================



    @Override
    public List<User> selectUserPage(UserPageDTO userPageDTO) {

        Integer page = userPageDTO.getPage();
        Integer limit = userPageDTO.getLimit();

        Integer start = (page - 1) * limit;


        String searchParams = userPageDTO.getSearchParams();

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        String sql = "select *  from owner  where 1=1 ";

        ArrayList<Object> params = new ArrayList<>();

        // 搜索分页
        if (searchParams != null) {
            //  username  address
            User user = JSONUtil.toBean(searchParams, User.class);
            String tel = user.getTel();
            String identity = user.getIdentity();
            System.out.println("identity = " + identity);
            if (!StringUtils.isEmptyOrWhitespaceOnly(tel)) {
                sql += " and tel = ?";
                params.add(tel);
            }
            if (!StringUtils.isEmptyOrWhitespaceOnly(identity)) {
                sql += " and identity = ?";
                params.add(identity);
            }
        }
        sql += " limit ? , ?";
        System.out.println("sql = " + sql);
        params.add(start);
        params.add(limit);
        System.out.println("params = " + params);

        try {
            List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class), params.toArray());
            return users;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("分页查询失败");
        }
    }

    @Override
    public Long selectUserTotal(UserPageDTO userPageDTO) {

        //查询总条数   搜索总条数
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        String sql = "select count(*) from owner ";

//        String searchParams = userPageDTO.getSearchParams();
//        ArrayList<Object> params = new ArrayList<>();

//        if(searchParams!=null){
//
//            User user = JSONUtil.toBean(searchParams, User.class);
//            String tel = user.getTel();
//            String identity = user.getIdentity();
//
//            if(!StringUtils.isEmptyOrWhitespaceOnly(tel)){
//                sql+=" and tel = ?";
//                params.add(tel);
//            }
//            if(!StringUtils.isEmptyOrWhitespaceOnly(identity)){
//                sql+=" and identity = ?";
//                params.add(identity);
//            }
//        }

        try {
            Long total = queryRunner.query(sql, new ScalarHandler<Long>());
            return total;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("查询总条数失败");
        }
    }

    @Override
    public User selectUserById(Integer id) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select *  from owner where id = ?";
        try {
            User user = queryRunner.query(sql, new BeanHandler<User>(User.class), id);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("查询数据失败！");
        }
    }

    @Override
    public void updateUser(User usr) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update owner set tel = ? ,sex = ?,identity =?,house_id=? where username = ?";
//        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
//        String strBirth = simpleDateFormat.format(usr.getBirth());

        try {
            queryRunner.update(sql, usr.getTel(), usr.getSex(), usr.getIdentity(), usr.getHouse_id(), usr.getUsername());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(CodeMsg.UPDATE_FAIL.getMsg());
        }

    }

    @Override
    public User selectUserByUserName(String username) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select * from owner  where username = ?";
        try {
            User user = queryRunner.query(sql, new BeanHandler<User>(User.class), username);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(CodeMsg.SELECT_FAIL.getMsg());
        }
    }

    @Override
    public void addUser(User usr) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "insert into owner (username,tel,sex,identity,house_id,password)values(?,?,?,?,?,?)";
//        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
//        String strBirth = simpleDateFormat.format(usr.getBirth());
        try {
            int update = queryRunner.update(sql, usr.getUsername(), usr.getTel(), usr.getSex(), usr.getIdentity(), usr.getHouse_id(),"00000");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(CodeMsg.ADD_FAIL.getMsg());
        }
    }

    @Override
    public void deleteUser(String ids) {  // 1,2,3  1

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "delete from owner where id = ?";

        String[] split = ids.split(",");
        ArrayList<Integer> allId = new ArrayList<>();
        for (String s : split) {
            allId.add(Integer.valueOf(s));
        }

        allId.forEach(id -> {
            try {
                queryRunner.update(sql, id);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                throw new RuntimeException(CodeMsg.DELETE_FAIL.getMsg());
            }
        });
    }


//========================================================================
// 姚俊涛
//========================================================================


    @Override
    public List<User> selectAdminPage1(UserPageDTO userPageDTO) {

        Integer page = userPageDTO.getPage();
        Integer limit = userPageDTO.getLimit();

        Integer start = (page - 1) * limit;


        String searchParams = userPageDTO.getSearchParams();

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        String sql = "select *from administrator  where 1=1 ";

        ArrayList<Object> params = new ArrayList<>();

        // 搜索分页
        if (searchParams != null) {

            Admin admin = JSONUtil.toBean(searchParams, Admin.class);
            long sno = admin.getSno();

            sql += " and sno = ?";
            params.add(sno);

        }
        sql += " limit ? , ?";
        params.add(start);
        params.add(limit);
        System.out.println("sql = " + sql);


        try {
            List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class), params.toArray());
            return users;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("分页查询失败");
        }
    }

    @Override
    public Long selectAdminTotal1(UserPageDTO userPageDTO) {

        //查询总条数   搜索总条数
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        String sql = "select count(*) from administrator where 1=1 ";

        String searchParams = userPageDTO.getSearchParams();
        ArrayList<Object> params = new ArrayList<>();

        if (searchParams != null) {

            Admin admin = JSONUtil.toBean(searchParams, Admin.class);
            long sno = admin.getSno();


            sql += " and sno = ?";
            params.add(sno);

        }

        try {
            Long total = queryRunner.query(sql, new ScalarHandler<Long>(), params.toArray());
            return total;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("查询总条数失败");
        }
    }

    @Override
    public List<Notice> selectAdminPage2(UserPageDTO userPageDTO) {

        Integer page = userPageDTO.getPage();
        Integer limit = userPageDTO.getLimit();

        Integer start = (page - 1) * limit;


        String searchParams = userPageDTO.getSearchParams();

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        String sql = "select *from notice  where 1=1 ";

        ArrayList<Object> params = new ArrayList<>();

        // 搜索分页
        if (searchParams != null) {

            Notice notice = JSONUtil.toBean(searchParams, Notice.class);
            String title = notice.getTitle();


            sql += " and title = ?";
            params.add(title);

        }
        sql += " limit ? , ?";
        params.add(start);
        params.add(limit);
        System.out.println("sql = " + sql);


        try {
            List<Notice> notices = queryRunner.query(sql, new BeanListHandler<Notice>(Notice.class), params.toArray());
            Boolean state = notices.get(0).getState();
            System.out.println(state);

            return notices;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("分页查询失败");
        }
    }

    @Override
    public Long selectAdminTotal2(UserPageDTO userPageDTO) {

        //查询总条数   搜索总条数
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        String sql = "select count(*) from notice where 1=1 ";

        String searchParams = userPageDTO.getSearchParams();
        ArrayList<Object> params = new ArrayList<>();

        if (searchParams != null) {

            Notice notice = JSONUtil.toBean(searchParams, Notice.class);
            String title = notice.getTitle();


            sql += " and title = ?";
            params.add(title);

        }

        try {
            Long total = queryRunner.query(sql, new ScalarHandler<Long>(), params.toArray());
            return total;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("查询总条数失败");
        }
    }

    @Override
    public void state(Boolean checked, Integer id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        String sql = "update notice set  state=? where id=?";
        int a = 0;
        if (checked) {
            a = 1;
        }
        try {
            int line = queryRunner.update(sql, a, id);
            System.out.println(line);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


    }


    @Override
    public Notice selectAdminById(Integer id) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select *  from notice where id = ?";
        try {
            Notice notice = queryRunner.query(sql, new BeanHandler<Notice>(Notice.class), id);
            return notice;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException("查询数据失败！");
        }
    }

    @Override
    public void updateAdmin(Notice notice) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update notice set content = ? ,xtime = ?,modify=? where title = ?";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String xt = simpleDateFormat.format(notice.getXtime());

        try {
            queryRunner.update(sql, notice.getContent(), xt, notice.getModify(), notice.getTitle());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(CodeMsg.UPDATE_FAIL.getMsg());
        }

    }

    @Override
    public Notice selectAdminByUserName(String title) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select * from notice  where title = ?";
        try {
            Notice notice = queryRunner.query(sql, new BeanHandler<Notice>(Notice.class), title);
            return notice;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(CodeMsg.SELECT_FAIL.getMsg());
        }
    }

    @Override
    public void addAdmin(Notice notice) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "insert into notice(title,content,publisher,ftime,modify,xtime,state)values(?,?,?,?,?,?,?)";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String ftime = simpleDateFormat.format(notice.getFtime());
        String xtime = simpleDateFormat.format(notice.getXtime());
        try {
            int update = queryRunner.update(sql, notice.getTitle(), notice.getContent(), notice.getPublisher(), ftime, notice.getModify(), xtime, notice.getState());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(CodeMsg.ADD_FAIL.getMsg());
        }
    }

    @Override
    public void deleteAdmin(String ids) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "delete from notice where id = ?";

        String[] split = ids.split(",");
        ArrayList<Integer> allId = new ArrayList<>();
        for (String s : split) {
            allId.add(Integer.valueOf(s));
        }

        allId.forEach(id -> {
            try {
                queryRunner.update(sql, id);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                throw new RuntimeException(CodeMsg.DELETE_FAIL.getMsg());
            }
        });
    }

    @Override
    public void updateAdminPwdById(String newPassword, Integer id) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update administrator set password = ?  where id = ?";
        try {
            queryRunner.update(sql, SecureUtil.md5(newPassword), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(CodeMsg.UPDATE_FAIL.getMsg());
        }

    }
}
